MySQL CREATE INDEX Statement

The indexes are used to retrieve the records of the table quickly by creating index to the column(s) of the table.

If index is not available then, MySQL must begin searching from first row and reads the entire table one row after the other.

CREATE INDEX Statement

The CREATE INDEX is used to create index for the columns.

Syntax for CREATE INDEX

CREATE INDEX index_name
ON table_name (column_1);

Example

CREATE INDEX idx_name 
ON employees (name);

This will create an index with name idx_name for the column name in employees table. This will accept duplicate values for this column.

To create index with unique values, use the following syntax.

CREATE UNIQUE INDEX index_name
ON table_name (column_1);

Example

CREATE UNIQUE INDEX idx_name 
ON employees (name);

It is also possible to create index with the combination of multiple columns.

Example

CREATE UNIQUE INDEX idx_name 
ON employees (firstname,lastname);

To remove an index from a column we will use DROP INDEX in the ALTER TABLE.

Syntax for DROP INDEX Statement

ALTER TABLE table_name
DROP INDEX index_name;

Example

ALTER TABLE employees
DROP INDEX idx_name;